/* TLSQL */

CREATE DATABASE AULA03
GO

USE AULA03
GO

CREATE TABLE CARROS(
	CARRO VARCHAR(20),
	FABRICANTE VARCHAR(30)
)
GO

INSERT INTO CARROS VALUES('KA','FORD')
INSERT INTO CARROS VALUES('FIESTA','FORD')
INSERT INTO CARROS VALUES('PRISMA','FORD')
INSERT INTO CARROS VALUES('CLIO','RENAULT')
INSERT INTO CARROS VALUES('SANDERO','RENAULT')
INSERT INTO CARROS VALUES('CHEVETE','CHEVROLET')
INSERT INTO CARROS VALUES('OMEGA','CHEVROLET')
INSERT INTO CARROS VALUES('PALIO','FIAT')
INSERT INTO CARROS VALUES('DOBLO','FIAT')
INSERT INTO CARROS VALUES('UNO','FIAT')
INSERT INTO CARROS VALUES('GOL','VOLKSWAGEN')

DECLARE
	@V_CONT_FORD INT,
	@V_CONT_FIAT INT
BEGIN
	SET @V_CONT_FORD = (SELECT COUNT(*) FROM CARROS
	WHERE FABRICANTE = 'FORD')
	
	SELECT @V_CONT_FIAT = COUNT(*) FROM CARROS WHERE
	FABRICANTE = 'FIAT'
	
	PRINT 'QUANTIDADE DE FORD: ' + CAST(@V_CONT_FORD AS VARCHAR)
	PRINT 'QUANTIDADE DE FIAT: ' + CONVERT(VARCHAR, @V_CONT_FIAT)
END
GO

/* USO DE IF */

DECLARE
	@NUMERO INT = 5
BEGIN
	IF @NUMERO = 5
		PRINT 'VALOR IGUAL A 5'
	ELSE
		PRINT 'VALOR DIFERENTE DE 5'
END

/* USO DO CASE */

DECLARE
	@FABRICANTE1 VARCHAR(30) = 'FIAT', --DECLARACAO COM ATRIBUICAO
	@FABRICANTE2 VARCHAR(30) = 'FORD',
	@FABRICANTE3 VARCHAR(30) --DECLARACAO
BEGIN
	SET @FABRICANTE3 = 'CHEVROLET' --ATRIBUICAO
	SELECT FABRICANTE,
	CASE
		WHEN FABRICANTE = @FABRICANTE1 THEN 'FAIXA 1'
		WHEN FABRICANTE = @FABRICANTE2 THEN 'FAIXA 2'
		WHEN FABRICANTE = @FABRICANTE3 THEN 'FAIXA 3'
		WHEN FABRICANTE = 'RENAULT' THEN 'FAIXA 4' --COMPARACAO
		ELSE 'OUTRAS FAIXAS'
	END AS "INFORMACOES", 10 + 10 AS "SOMA", CARRO
	FROM CARROS
END
GO

/* PROCEDURE */

DROP PROCEDURE FAIXAS
GO

CREATE PROCEDURE FAIXAS @P_FAB1 VARCHAR(30), @P_FAB2 VARCHAR(30)
AS
DECLARE
	@VFABRICANTE1 VARCHAR(30) = @P_FAB1, --DECLARACAO COM ATRIBUICAO
	@VFABRICANTE2 VARCHAR(30) = @P_FAB2,
	@VFABRICANTE3 VARCHAR(30) --DECLARACAO
BEGIN
	SET @VFABRICANTE3 = 'CHEVROLET' --ATRIBUICAO
	SELECT FABRICANTE,
	CASE
		WHEN FABRICANTE = @VFABRICANTE1 THEN 'FAIXA 1'
		WHEN FABRICANTE = @VFABRICANTE2 THEN 'FAIXA 2'
		WHEN FABRICANTE = @VFABRICANTE3 THEN 'FAIXA 3'
		WHEN FABRICANTE = 'RENAULT' THEN 'FAIXA 4' --COMPARACAO
		ELSE 'OUTRAS FAIXAS'
	END AS "INFORMACOES", 10 + 10 AS "SOMA", CARRO
	FROM CARROS
	
END
GO

FAIXAS 'CHEVROLET','FIAT'

/* WHILE, BREAK, CONTINUE */

DECLARE
	@I INT = 1
BEGIN
	WHILE (@I < 10)
	BEGIN
		PRINT 'VALOR DE @I = ' + CAST(@I AS VARCHAR)
		SET @I = @I + 1
	END
END
GO


CREATE PROCEDURE FABRICANTE @P_FABRICANTE VARCHAR(30) AS
DECLARE
	@I INT,
	@V_FABRICANTE VARCHAR(30)
BEGIN
	
	SET @V_FABRICANTE = @P_FABRICANTE
	SET @I = (SELECT COUNT(*) FROM CARROS
	WHERE FABRICANTE = @V_FABRICANTE)
	
	WHILE (@I < 10)
	BEGIN
		PRINT 'VALOR DE @I = ' + CAST(@I AS VARCHAR)
		SET @I = @I + 1
	END
END
GO

FABRICANTE 'RENAULT'

/* WHILE COM BREAK E CONTINUE */

SELECT ROUND(RAND(),2)

DECLARE
	@I INT = 1
BEGIN
	WHILE @I < 200
	
	BEGIN
	
		SET @I = @I + ROUND(RAND() * 10, 2) + 1
		PRINT 'VALOR DE @I = ' + CAST(@I AS VARCHAR)
		
			IF @I > 200
				BREAK
			ELSE
				PRINT 'BLOCO ELSE'
				CONTINUE
			
	END
END
GO

/* CRIANDO TABELAS */

CREATE TABLE PAIS(
	IDPAIS INT PRIMARY KEY IDENTITY,
	PAIS VARCHAR(30)
)
GO

ALTER TABLE CARROS ADD PAIS INT
GO

INSERT INTO PAIS VALUES('EUA')
INSERT INTO PAIS VALUES('ITALIA')
INSERT INTO PAIS VALUES('FRANCA')
INSERT INTO PAIS VALUES('ALEMANHA')

INSERT INTO CARROS VALUES('PASSAT','VOLKSWAGEN', 4)
GO

UPDATE CARROS SET PAIS = 1
WHERE FABRICANTE = 'FORD'
OR FABRICANTE = 'CHEVROLET'
GO

UPDATE CARROS SET PAIS = 2
WHERE FABRICANTE = 'FIAT'
GO

UPDATE CARROS SET PAIS = 3
WHERE FABRICANTE = 'PEUGEOT'
OR FABRICANTE = 'RENAULT'
GO

UPDATE CARROS SET PAIS = 4
WHERE FABRICANTE = 'VOLKSWAGEN'
GO

ALTER TABLE CARROS ADD CONSTRAINT FK_CARROS_PAIS
FOREIGN KEY(PAIS) REFERENCES PAIS(IDPAIS)
GO



/* CRIACAO DE VIEWS */

CREATE VIEW V_CARROS AS
SELECT C.CARRO, C.FABRICANTE, P.PAIS
FROM CARROS C
INNER JOIN PAIS P
ON C.PAIS = P.IDPAIS
GO

SELECT * FROM V_CARROS
GO

/* VIEW COM ORDER BY */
CREATE VIEW V_PAIS AS
SELECT TOP 100 PERCENT C.CARRO, C.FABRICANTE, P.PAIS
FROM CARROS C
INNER JOIN PAIS P
ON C.PAIS = P.IDPAIS
ORDER BY P.PAIS
GO

/* ORDENA APENAS SEM A CLAUSLA PERCENT */

DROP VIEW V_PAIS

SELECT TOP 2  * FROM V_CARROS

CREATE VIEW V_PAIS AS
SELECT TOP (SELECT COUNT(*) FROM CARROS) C.CARRO, C.FABRICANTE, P.PAIS
FROM CARROS C
INNER JOIN PAIS P
ON C.PAIS = P.IDPAIS
ORDER BY P.PAIS
GO

SELECT * FROM V_PAIS

/* A VIEW ORDENADA FUNCIONA APENAS SEM A CLAUSULA PERCENT */

DECLARE 
	@I INT = 10
SELECT TOP(@I) * FROM V_PAIS

/* DELETANDO UM OBJETO CASO EXISTA  */

IF OBJECT_ID('PESSOA') IS NOT NULL
DROP TABLE PESSOA
GO

CREATE TABLE PESSOA(
	NOME VARCHAR(30),
	SOBRENOME VARCHAR(30)
)
GO

INSERT INTO PESSOA VALUES('LEO','TEIXEIRA')
INSERT INTO PESSOA VALUES('RENATO','AMARAL')
INSERT INTO PESSOA VALUES('STUART','LITTLE')
GO

CREATE VIEW V_PESSOA AS
	SELECT * FROM PESSOA
GO

CREATE VIEW V_PESSOA2 AS
	SELECT *, LEFT(NOME, 1) AS PRIMEIRALETRA
	 FROM PESSOA
GO

SELECT * FROM PESSOA
SELECT * FROM V_PESSOA
SELECT * FROM V_PESSOA2

ALTER TABLE PESSOA
ADD ANIVERSARIO DATETIME
GO

UPDATE PESSOA
SET ANIVERSARIO = '1997-01-20'
GO

SELECT * FROM PESSOA
GO

SELECT * FROM V_PESSOA
SELECT * FROM V_PESSOA2
GO

SP_REFRESHVIEW V_PESSOA
GO
SP_REFRESHVIEW V_PESSOA2
GO 

SP_HELPTEXT SP_REFRESHVIEW
GO

SP_HELPTEXT V_CARROS
GO

/* VIEW COM CRIPTOGRAFIA */

ALTER VIEW V_CARROS WITH ENCRYPTION
AS
SELECT C.CARRO, C.FABRICANTE, P.PAIS
FROM CARROS C
INNER JOIN PAIS P
ON C.PAIS = P.IDPAIS
GO

SELECT * FROM V_CARROS

SP_HELPTEXT V_CARROS
GO

/* VIEW COM SCHEMABINDING */

ALTER VIEW V_CARROS WITH ENCRYPTION, SCHEMABINDING
AS
SELECT C.CARRO, C.FABRICANTE, P.PAIS
FROM DBO.CARROS C
INNER JOIN DBO.PAIS P
ON C.PAIS = P.IDPAIS
GO

ALTER TABLE CARROS
DROP COLUMN CARRO
GO

/* VIEW COM CHECK */

CREATE TABLE CIDADES(
	IDCIDADE INT IDENTITY,
	CIDADE VARCHAR(30)
)
GO

INSERT INTO CIDADES VALUES('RIO DE JANEIRO')
INSERT INTO CIDADES VALUES('SAO PAULO')
INSERT INTO CIDADES VALUES('BELO HORIZONTE')
INSERT INTO CIDADES VALUES('LONDRINA')
GO

CREATE VIEW V_CIDADES
AS
SELECT * FROM CIDADES
WHERE CIDADE > 'M'
GO

SELECT * FROM V_CIDADES

INSERT INTO V_CIDADES VALUES('BRAGANCA')
GO

SELECT * FROM V_CIDADES

DELETE FROM V_CIDADES WHERE CIDADE = 'BAURU'
GO

SELECT * FROM CIDADES
GO

ALTER VIEW V_CIDADES
AS
SELECT * FROM CIDADES
WHERE CIDADE > 'M'
WITH CHECK OPTION
